package com.dragonsoftbravo.sqlbuilder;

import java.net.MalformedURLException;
import java.net.URL;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.dragonsoftbravo.sqlbuilder.dao.SqlBuilder;
import com.dragonsoftbravo.sqlbuilder.sql.ArgPair;
import com.dragonsoftbravo.sqlbuilder.sql.where.builder.Comp;
import com.dragonsoftbravo.sqlbuilder.sql.where.builder.Condition;
import jdk.internal.util.xml.impl.Pair;

public class Test {

//    class MysqlConnector {
//        Connection conn = null;
//
//        MysqlConnector() {
//            try {
//                Class.forName("com.mysql.jdbc.Driver");// 动态加载mysql驱动
//                System.out.println("成功加载MySQL驱动程序");
//                conn =
//                        DriverManager.getConnection("jdbc:mysql://localhost/databaseName?user=root&password=abcd1234&useUnicode=true&characterEncoding=utf-8");
//            } catch (SQLException ex) {
//                // handle any errors
//                System.out.println("SQLException: " + ex.getMessage());
//                System.out.println("SQLState: " + ex.getSQLState());
//                System.out.println("VendorError: " + ex.getErrorCode());
//            } catch (ClassNotFoundException e) {
//                e.printStackTrace();
//            }
//            System.out.println("连接成功");
//        }
//
//        public Connection getConn() {
//            return conn;
//        }
//
//        void close() {
//            try {
//                if (conn != null && !conn.isClosed()) {
//                    conn.close();
//                }
//            } catch (SQLException e) {
//                e.printStackTrace();
//            }
//        }
//    }
//
//    public void mTest() {
//        MysqlConnector connector = new MysqlConnector();
//        Connection conn = connector.getConn();
//
//        Statement stmt = null;
//        ResultSet rs = null;
//
//        try {
//            stmt = conn.createStatement();
//            String sql = new SqlBuilder("user").query().limit(1).println();
//            //sql = "select * from user where gender='男' limit 10";
//            rs = stmt.executeQuery(sql + ";");
//            System.out.println(sql);
//            int i = 1;
//            while (rs.next()) {
//                System.out.println((i++) + "," + rs.getString("id") + "," + rs.getString("name"));
//            }
//
//            sql = new SqlBuilder("user").update(new ArgPair().pair("name", "测试")).where(Comp.equal("id", 33)).println();
//            stmt.execute(sql);
//
//            sql = new SqlBuilder("user").query().where(Comp.equal("id", 33)).println();
//            rs = stmt.executeQuery(sql);
//            while (rs.next()) {
//                System.out.println("id:" + rs.getInt("id") + ";name:" + rs.getString("name"));
//            }
//            // Now do something with the ResultSet ....
//        } catch (SQLException ex) {
//            // handle any errors
//            System.out.println("SQLException: " + ex.getMessage());
//            System.out.println("SQLState: " + ex.getSQLState());
//            System.out.println("VendorError: " + ex.getErrorCode());
//        } finally {
//            // it is a good idea to release
//            // resources in a finally{} block
//            // in reverse-order of their creation
//            // if they are no-longer needed
//
//            if (rs != null) {
//                try {
//                    rs.close();
//                } catch (SQLException sqlEx) {
//                } // ignore
//
//                rs = null;
//            }
//
//            if (stmt != null) {
//                try {
//                    stmt.close();
//                } catch (SQLException sqlEx) {
//                } // ignore
//
//                stmt = null;
//            }
//        }
//
//        connector.close();
//    }

    /**
     * @param args
     */
    public static void main(String[] args) throws MalformedURLException {
        new Test().test();
    }

    String sql;

    public void test() {
        // v1.1
        System.out.println("================================v1.1===================================");
        //insert
        sql = new SqlBuilder("user")
                .insert(new ArgPair().pair("number", 1101).pair("name", "zhangsan").pair("age", 24).pair("score", 60))
                .println();
        //delete
        sql = new SqlBuilder("user")
                .delete()
                .where(Condition.or(Comp.equal("name", "zhangsan"), Condition.or(Comp.lt("age", 20), Comp.gt("age", 38))))
                .println();
        //delete all data;
        sql = new SqlBuilder("user")
                .delete()
                .println();
        //update
        sql = new SqlBuilder("user")
                .update(new ArgPair().pair("score", 90))
                .where(Comp.like("name", "张%"))
                .println();
        //query
        sql = new SqlBuilder("user")
                .query("number", "name", "score"/*defalt '*' if blank */)
                .where(/*you can replace your condition here or just leave it blank if you didn't want any condition' or simply just not user 'where()' */)
                .limit(100)
                .desc("score")
                .asc("name")
                .println();
    }
}
